﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.IO;

namespace foursquare
{
    class FSLocal
    {
        //path = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
        private static string CONNECTION_STRING;// = @"data source=\Program Files\foursquare\FS.db";
        private static string APP_DATA;
        private static string DBNAME = "FS.db";

        public static void InitDatabase()
        {
            APP_DATA = Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData) + @"\foursquare\";

            CONNECTION_STRING = "data source=" + APP_DATA + DBNAME;

            if (!File.Exists(APP_DATA))
            {
                Directory.CreateDirectory(APP_DATA);
            }
            if (!File.Exists(APP_DATA + DBNAME))
            {
                CreateDatabase();
            }
        }

        private static void CreateDatabase()
        {
            SQLiteConnection.CreateFile(APP_DATA + DBNAME);

            StringBuilder tblSettings = new StringBuilder("CREATE TABLE [settings] (");
            tblSettings.Append("[username] varchar(150) NOT NULL,");
            tblSettings.Append("[password] varchar(150) NOT NULL);");

            StringBuilder tblJson = new StringBuilder("CREATE TABLE [json] (");
            tblJson.Append("[jsonType] varchar(50) NOT NULL,");
            tblJson.Append("[jsonParam] text NOT NULL,");
            tblJson.Append("[jsonBody] text NOT NULL,");
            tblJson.Append("[datecreated] datetime NOT NULL,");
            tblJson.Append("CONSTRAINT [PK_json] PRIMARY KEY ([jsonType], [jsonParam]));");


            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();

            SQLiteCommand cmd = new SQLiteCommand(tblSettings.ToString(), _conn);
            SQLiteCommand cmd2 = new SQLiteCommand(tblJson.ToString(), _conn);
            
            cmd.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();
            
            _conn.Close();
        }

        public static void SaveJSON(string jsonType, string jsonParam, string jsonBody)
        {
            string command = "insert into json (jsonType, jsonParam, jsonBody, datecreated) values (@jsonType, @jsonParam, @jsonBody, datetime('now'))";

            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();

            int rs;
            SQLiteCommand cmd = new SQLiteCommand(command, _conn);

            cmd.Parameters.Add(new SQLiteParameter("@jsonType", jsonType));
            cmd.Parameters.Add(new SQLiteParameter("@jsonParam", jsonParam));
            cmd.Parameters.Add(new SQLiteParameter("@jsonBody", jsonBody));
            rs = cmd.ExecuteNonQuery();
            _conn.Close();
        }

        public static string GetJSON(string jsonType, string jsonParam)
        {
            ClearJSON(jsonType);

            string command = "select jsonBody from json where jsonType=@jsonType and jsonParam=@jsonParam";
            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();
            string rs;
            SQLiteCommand cmd = new SQLiteCommand(command, _conn);
            cmd.Parameters.Add(new SQLiteParameter("@jsonType", jsonType));
            cmd.Parameters.Add(new SQLiteParameter("@jsonParam", jsonParam));
            try
            {
                rs = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                rs = String.Empty;
            }
            _conn.Close();
            return rs;
        }

        public static void ClearJSON(string jsonType)
        {
            string command = "delete from json where jsonType=@jsonType and datecreated > datetime('now','+10 minutes')";

            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();

            int rs;
            SQLiteCommand cmd = new SQLiteCommand(command, _conn);

            cmd.Parameters.Add(new SQLiteParameter("@jsonType", jsonType));
            rs = cmd.ExecuteNonQuery();
            _conn.Close();
        }

        public static void LoadSavedCredentials()
        {
            string command = "select username, password from settings";
            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();
            SQLiteDataReader rs;
            SQLiteCommand cmd = new SQLiteCommand(command, _conn);
            try
            {
                rs = cmd.ExecuteReader();
                if (rs.Read())
                {

                    FS.OAuthToken = rs["username"].ToString();
                    FS.OAuthTokenSecret = rs["password"].ToString();
                }
            }
            catch (Exception ex)
            {
                
            }
            _conn.Close();
        }

        public static void SaveCredentials(string username, string password)
        {
            string dcommand = "delete from settings";

            SQLiteConnection _conn = new SQLiteConnection(CONNECTION_STRING);
            _conn.Open();

            int drs;
            SQLiteCommand dcmd = new SQLiteCommand(dcommand, _conn);
            drs = dcmd.ExecuteNonQuery();

            string command = "insert into settings (username, password) values (@username, @password)";

            int rs;
            SQLiteCommand cmd = new SQLiteCommand(command, _conn);

            cmd.Parameters.Add(new SQLiteParameter("@username", username));
            cmd.Parameters.Add(new SQLiteParameter("@password", password));
            rs = cmd.ExecuteNonQuery();
            _conn.Close();
        }
    }
}
